﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using ESRI.ArcGIS.Framework;
using ESRI.ArcGIS.Carto;
using ESRI.ArcGIS.DataSourcesGDB;
using BesAsm.Framework.ArcGisUtilities;
using BesAsm.Framework.MsAccessUtilities;
using BesAsm.MasterData.Entities;
using BesAsm.MasterData.Services;
using BesAsm.Emgaats.Core;
using BesAsm.Framework.Reporting;

namespace BesAsm.Emgaats.ModelBuilder
{
  public class PgdbBuilder
  {
    private Model _model;
    private EmgaatsLayerCollection _layers;
    private IApplication _arcApp;
    private AccessHelper _accessHelper;
    private const string MODEL_BUILD_ACCESS_DB = "ModelBuild.mdb";

    public PgdbBuilder(Model model, IApplication arcApp)
    {
      _model = model;
      _layers = LayerLibrarian.Library;
      _arcApp = arcApp;
      //_layers = LayerLibrarian.
    }

    public void WritePgdb()
    {
      _accessHelper = null;
      try
      {
        string pgdbPath = _model.GetPathIfSet(ModelPathComponent.Pgdb);
        _accessHelper = new AccessHelper(pgdbPath);

        TList<Link> traceLinks = GetTraceLinks();

        IEnumerable<int> linkIds = WriteLinks(traceLinks);
        IEnumerable<int> nodeIds = WriteNodes(traceLinks);

        WriteDscs(linkIds);
        WriteSscs(nodeIds);

        WriteIcTargets(nodeIds);

        WriteTabularData();

        BuildIndices();

        WriteModelBuildDatabase();

        RunModelBuildQueries();
        
      }
      catch (Exception ex)
      {
        throw ex; //TODO: Remove this or handle exception (used now to place breakpoint for debugging)
      }
      finally
      {
        if (_accessHelper != null)
          _accessHelper.Dispose();
      }
    }

    private void WriteModelBuildDatabase()
    {
      Messenger.ReportMessage("Copying ModelBuild.mdb", ReportableMessageType.Info);
      FileStream outStream = null;
      try
      {
        string path = Path.Combine(_model.GetPathIfSet(ModelPathComponent.RootFolder), MODEL_BUILD_ACCESS_DB);
        outStream = new FileStream(path, FileMode.Create);
        byte[] data = Properties.Resources.ModelBuild;
        outStream.Write(data, 0, data.Length);
        outStream.Flush();
      }
      finally
      {
        if (outStream != null)
          outStream.Close();
      }
    }

    private void BuildIndices()
    {
      CreatePrimaryIndexForLayer(LibraryItem.ModelLinks, "link_id");
      CreatePrimaryIndexForLayer(LibraryItem.ModelNodes, "node_id");
      CreatePrimaryIndexForLayer(LibraryItem.ModelDscs, "dsc_id");
      CreatePrimaryIndexForLayer(LibraryItem.ModelSscs, "ssc_id");
      CreatePrimaryIndexForLayer(LibraryItem.ModelParkingTargets, "parking_target_id");
      CreatePrimaryIndexForLayer(LibraryItem.ModelRoofTargets, "roof_target_id");
      CreatePrimaryIndexForLayer(LibraryItem.ModelStreetTargets, "street_target_id");

      CreatePrimaryIndexForTable("MODEL_SPECIAL_LINKS", "special_link_id");
      CreatePrimaryIndexForTable("MODEL_SPECIAL_LINKS_DATA", "special_link_data_id");
      CreatePrimaryIndexForTable("MODEL_PIPE_SHAPES", "shape_id");
      CreatePrimaryIndexForTable("MODEL_PIPE_SHAPES_DATA", "udg_id");
      CreatePrimaryIndexForTable("MODEL_PUMPS", "pump_id");
      CreatePrimaryIndexForTable("MODEL_PUMP_CURVES", "pump_curve_id");
      CreatePrimaryIndexForTable("MODEL_SAN_PATTERNS", "san_pattern_id");
      CreatePrimaryIndexForTable("MODEL_CROSS_SECTIONS", "cross_section_id");
      CreatePrimaryIndexForTable("MODEL_CROSS_SECTIONS_DATA", "cross_section_data_id");
      CreatePrimaryIndexForTable("MODEL_IC_DISCOS", "ic_disco_id");
      CreatePrimaryIndexForTable("MODEL_IC_DRYWELLS", "ic_drywell_id");
      CreatePrimaryIndexForTable("MODEL_IC_NODE_REDIRECTORS", "ic_node_redirector_id");

    }

    private TList<Link> GetTraceLinks()
    {
      Messenger.ReportMessage("Reading trace", ReportableMessageType.Info);

      EmgaatsLayer traceLinksLayer = _layers[LibraryItem.TraceLinks];
      EmgaatsList<Link> traceLinks = new EmgaatsList<Link>(_arcApp, traceLinksLayer);
      traceLinks.SyncListToMap(new LinkService());

      return traceLinks;
    }

    /// <summary>
    /// Writes the links collection to the PGDB and deep loads the links collection
    /// </summary>
    /// <returns>A deep-loaded collection of the model links</returns>
    private IEnumerable<int> WriteLinks(TList<Link> traceLinks)
    {
      Messenger.ReportMessage("Writing links/nodes", ReportableMessageType.Info);

      IEnumerable<int> linkOids = traceLinks.Select(p => p.Oid);

      WriteModelLayer(LibraryItem.MasterLinks,
         LibraryItem.ModelLinks, "link_id", linkOids);

      IEnumerable<int> linkIds = traceLinks.Select(p => p.LinkId);
      return linkIds;
    }

    private IEnumerable<int> WriteNodes(TList<Link> traceLinks)
    {
      NodeService ns = new NodeService();
      IEnumerable<int> nodeIds =
        traceLinks.Select(p => p.UsNodeId)
        .Union(traceLinks.Select(p => p.DsNodeId));
      IEnumerable<int> nodeOids = ns.GetOidsByNodeIds(nodeIds);

      WriteModelLayer(LibraryItem.MasterNodes,
        LibraryItem.ModelNodes, "node_id", nodeOids);

      return nodeIds;
    }

    /// <summary>
    /// Writes the nodes and dscs collections to the PGDB and deep loads the nodes
    /// child entities (sscs and ic targets)
    /// </summary>
    /// <param name="traceLinks">A deep-loaded collection of the model links</param>
    /// <returns>A deep-loaded collection of the model nodes</returns>
    private void WriteDscs(IEnumerable<int> linkIds)
    {
      Messenger.ReportMessage("Writing dscs", ReportableMessageType.Info);

      DscService ds = new DscService();
      IEnumerable<int> dscOids = ds.GetOidsByLinkIds(linkIds);

      WriteModelLayer(LibraryItem.MasterDscs,
        LibraryItem.ModelDscs, "dsc_id", dscOids);

      return;
    }

    private void WriteSscs(IEnumerable<int> nodeIds)
    {
      Messenger.ReportMessage("Writing sscs", ReportableMessageType.Info);

      SscService ss = new SscService();
      IEnumerable<int> sscOids = ss.GetOidsByNodeIds(nodeIds, 
        _model.TimeFrame.ToUpper() == "EXISTING" ? "EX" : "FU");

      WriteModelLayer(LibraryItem.MasterSscs,
        LibraryItem.ModelSscs, "ssc_id", sscOids);

      return;
    }

    /// <summary>
    /// Writes the ic targets collection to the PGDB
    /// </summary>
    /// <param name="traceNodes">A deep-loaded collection of the model nodes</param>
    private void WriteIcTargets(IEnumerable<int> nodeIds)
    {
      Messenger.ReportMessage("Writing ic targets", ReportableMessageType.Info);

      ParkingTargetService pts = new ParkingTargetService();
      RoofTargetService rts = new RoofTargetService();
      StreetTargetService sts = new StreetTargetService();

      IEnumerable<int> parkingTargetOids = pts.GetOidsByNodeIds(nodeIds);
      IEnumerable<int> roofTargetOids = rts.GetOidsByNodeIds(nodeIds);
      IEnumerable<int> streetTargetOids = sts.GetOidsByNodeIds(nodeIds);

      WriteModelLayer(LibraryItem.MasterParkingTargets,
        LibraryItem.ModelParkingTargets, "parking_target_id", parkingTargetOids);

      WriteModelLayer(LibraryItem.MasterRoofTargets,
        LibraryItem.ModelRoofTargets, "roof_target_id", roofTargetOids);

      WriteModelLayer(LibraryItem.MasterStreetTargets,
        LibraryItem.ModelStreetTargets, "street_target_id", streetTargetOids);
    }

    private void WriteModelLayer(
      LibraryItem sourceItem,
      LibraryItem destinationItem,
      string destinationPrimaryKeyName,
      IEnumerable<int> oids)
    {
      Messenger.ReportMessage("Writing model layer " + destinationItem.ToString(), ReportableMessageType.Info);

      EmgaatsLayer sourceLayer = _layers[sourceItem];

      sourceLayer.SelectFeatures(oids.ToArray(), true);

      string modelLayer = _model.GetModelLayerName(destinationItem);
      string modelPath = _model.GetPathIfSet(ModelPathComponent.Pgdb);

      sourceLayer.SaveSelectionToPgdb(modelLayer, modelPath);
    }

    private void CreatePrimaryIndexForLayer(LibraryItem destinationItem, string destinationPrimaryKeyName)
    {
      string pgdbPath = _model.GetPathIfSet(ModelPathComponent.Pgdb);

      string layerName = _model.GetModelLayerName(destinationItem);
      _accessHelper.AppendIndex(layerName, "primary", true, true, true, false,
        new List<string>() { destinationPrimaryKeyName });
    }

    /// <summary>
    /// Writes the non-spatial model data tables
    /// </summary>
    /// <param name="traceLinks">The trace links.</param>
    /// <param name="traceNodes">The trace nodes.</param>
    private void WriteTabularData()
    {
      Messenger.ReportMessage("Writing tabular data ", ReportableMessageType.Info);

      string pgdbPath = _model.GetPathIfSet(ModelPathComponent.Pgdb);

      string masterSQLConnectionString = Mediator.MasterConnectionString;
      Dictionary<string, string> masterConnectionStringPieces =
        masterSQLConnectionString.Split(';')
        .Select(x => x.Split('='))
        .ToDictionary(y => y[0], y => y[1]);
      string linkConnectionString =
        string.Format("Server={0};Database={1};Trusted_Connection=Yes",
          masterConnectionStringPieces["Data Source"],
          masterConnectionStringPieces["Initial Catalog"]);

      //TODO: hard-coded table names below should be read from config files
      try
      {
        WriteModelTable(linkConnectionString, "GIS.SPECIAL_LINKS",
          "MODEL_SPECIAL_LINKS", "special_link_id", "to_link_id", "MODEL_LINKS", "link_id");

        WriteModelTable(linkConnectionString, "GIS.SPECIAL_LINKS_DATA",
          "MODEL_SPECIAL_LINKS_DATA", "special_link_data_id", "special_link_id",
          "MODEL_SPECIAL_LINKS", "special_link_id");

        WriteModelTable(linkConnectionString, "GIS.PIPE_SHAPES",
          "MODEL_PIPE_SHAPES", "shape_id");

        WriteModelTable(linkConnectionString, "GIS.PIPE_SHAPES_DATA",
          "MODEL_PIPE_SHAPES_DATA", "udg_id");

        WriteModelTable(linkConnectionString, "GIS.PUMPS",
          "MODEL_PUMPS", "pump_id", "link_id", "MODEL_LINKS", "link_id");

        WriteModelTable(linkConnectionString, "GIS.PUMP_CURVES",
          "MODEL_PUMP_CURVES", "pump_curve_id", "pump_id", "MODEL_PUMPS", "link_id");

        //TODO: SAN_PATTERNS has no foreign-key relationship to a model table. Future work should link SAN_PATTERN to a DSC or a basin
        WriteModelTable(linkConnectionString, "GIS.SAN_PATTERNS",
          "MODEL_SAN_PATTERNS", "san_pattern_id");

        WriteModelTable(linkConnectionString, "GIS.CROSS_SECTIONS",
          "MODEL_CROSS_SECTIONS", "cross_section_id", "link_id", "MODEL_LINKS", "link_id");

        WriteModelTable(linkConnectionString, "GIS.CROSS_SECTIONS_DATA",
          "MODEL_CROSS_SECTIONS_DATA", "cross_section_data_id", "cross_section_id",
          "MODEL_CROSS_SECTIONS", "cross_section_id");

        WriteModelTable(linkConnectionString, "GIS.IC_DISCOS",
          "MODEL_IC_DISCOS", "ic_disco_id", "dsc_id", "MODEL_DSCS", "dsc_id");

        WriteModelTable(linkConnectionString, "GIS.IC_DRYWELLS",
          "MODEL_IC_DRYWELLS", "ic_drywell_id", "dsc_id", "MODEL_DSCS", "dsc_id");

        WriteModelTable(linkConnectionString, "GIS.IC_NODE_REDIRECTORS",
          "MODEL_IC_NODE_REDIRECTORS", "ic_node_redirector_id", "ssc_id", "MODEL_SSCS", "ssc_id");

        //TODO: Write any other tabular data, if any missed here

      }
      catch (Exception ex)
      {
        throw ex;
      }

    }

    private void WriteModelTable(
      string masterConnectionString,
      string masterTable,
      string modelTable,
      string modelPrimaryKey)
    {
      Messenger.ReportMessage("Writing tabular data " + modelTable, ReportableMessageType.Info);

      const string TEMP_TABLE_NAME = "_tempTable";
      const string TEMP_QUERY_NAME = "_tempQuery";

      _accessHelper.LinkTable(masterTable, masterConnectionString, TEMP_TABLE_NAME, ExternalDatabaseType.SqlServer);
      _accessHelper.CopyTableSchema(TEMP_TABLE_NAME, modelTable);
      _accessHelper.CreateQuery(TEMP_QUERY_NAME,
        "INSERT INTO " + modelTable + " " +
        "SELECT [" + TEMP_TABLE_NAME + "].* " +
        "FROM [" + TEMP_TABLE_NAME + "];");

      _accessHelper.ExecuteActionQuery(TEMP_QUERY_NAME);
      _accessHelper.DeleteTable(TEMP_TABLE_NAME);
      _accessHelper.DeleteQuery(TEMP_QUERY_NAME);
    }

    private void CreatePrimaryIndexForTable(string tableName, string tablePrimaryKeyName)
    {
      string pgdbPath = _model.GetPathIfSet(ModelPathComponent.Pgdb);

      _accessHelper.AppendIndex(tableName, "primary", true, true, true, false,
        new List<string>() { tablePrimaryKeyName });

    }

    private void WriteModelTable(
      string masterConnectionString,
      string masterTable,
      string modelTable,
      string modelPrimaryKey,
      string modelJoinKey,
      string joinTable,
      string joinPrimaryKey)
    {
      Messenger.ReportMessage("Writing tabular data " + modelTable + " join " + joinTable, ReportableMessageType.Info);

      const string TEMP_TABLE_NAME = "_tempTable";
      const string TEMP_QUERY_NAME = "_tempQuery";

      _accessHelper.LinkTable(masterTable, masterConnectionString, TEMP_TABLE_NAME, ExternalDatabaseType.SqlServer);
      _accessHelper.CopyTableSchema(TEMP_TABLE_NAME, modelTable);
      _accessHelper.CreateQuery(TEMP_QUERY_NAME,
        "INSERT INTO " + modelTable + " " +
        "SELECT [" + TEMP_TABLE_NAME + "].* " +
        "FROM [" + TEMP_TABLE_NAME + "] INNER JOIN [" + joinTable + "] " +
        "ON [" + TEMP_TABLE_NAME + "].[" + modelJoinKey + "] = [" + joinTable + "].[" + joinPrimaryKey + "];");

      _accessHelper.ExecuteActionQuery(TEMP_QUERY_NAME);
      _accessHelper.DeleteTable(TEMP_TABLE_NAME);
      _accessHelper.DeleteQuery(TEMP_QUERY_NAME);
    }

    private void RunModelBuildQueries()
    {
      Messenger.ReportMessage("Running dsc spatial queries", ReportableMessageType.Info);

      AccessHelper modelBuild = null;

      try
      {
        string modelBuildDbPath = System.IO.Path.Combine(
        _model.GetPathIfSet(ModelPathComponent.RootFolder), MODEL_BUILD_ACCESS_DB);

        modelBuild = new AccessHelper(modelBuildDbPath);

        modelBuild.UpdateField("TimeFrame", "[value]",
          _model.TimeFrame.ToUpper() == "EXISTING" ? "EX" : "FU");

        modelBuild.LinkTable("MODEL_DSCS", _model.GetPathIfSet(ModelPathComponent.Pgdb), ExternalDatabaseType.MsAccess);
        modelBuild.LinkTable("MODEL_SSCS", _model.GetPathIfSet(ModelPathComponent.Pgdb), ExternalDatabaseType.MsAccess);
        modelBuild.LinkTable("MODEL_IC_DISCOS", _model.GetPathIfSet(ModelPathComponent.Pgdb), ExternalDatabaseType.MsAccess);
        modelBuild.LinkTable("MODEL_IC_DRYWELLS", _model.GetPathIfSet(ModelPathComponent.Pgdb), ExternalDatabaseType.MsAccess);
        modelBuild.LinkTable("MODEL_NODES", _model.GetPathIfSet(ModelPathComponent.Pgdb), ExternalDatabaseType.MsAccess);
        modelBuild.LinkTable("MODEL_LINKS", _model.GetPathIfSet(ModelPathComponent.Pgdb), ExternalDatabaseType.MsAccess);

        modelBuild.ExecuteActionQuery("APPEND_TO_MODEL_DSC_CALCS");
        modelBuild.ExecuteActionQuery("APPEND_TO_MODEL_SSC_CALCS");

        if (_model.TimeFrame.ToUpper() == "EXISTING")
          modelBuild.ExecuteActionQuery("EMG3_UPDATE_DSC_CALCS_EX");
        else                                         
          modelBuild.ExecuteActionQuery("EMG3_UPDATE_DSC_CALCS_FU");
              
        Messenger.ReportMessage("Running ssc spatial queries", ReportableMessageType.Info);
                
        string pgdbPath = _model.GetPathIfSet(ModelPathComponent.Pgdb);

        ESRI.ArcGIS.Geodatabase.IWorkspaceFactory workspaceFactory = new AccessWorkspaceFactoryClass();
        ESRI.ArcGIS.Geodatabase.IFeatureWorkspace fws
          = (ESRI.ArcGIS.Geodatabase.IFeatureWorkspace)workspaceFactory.OpenFromFile(pgdbPath, 0);

        ESRI.ArcGIS.Geodatabase.IFeatureClass dscs
          = (ESRI.ArcGIS.Geodatabase.IFeatureClass)fws.OpenFeatureClass("MODEL_DSCS");
        ESRI.ArcGIS.Geodatabase.IFeatureClass sscs
          = (ESRI.ArcGIS.Geodatabase.IFeatureClass)fws.OpenFeatureClass("MODEL_SSCS");        

        DataTable dt;
        dt = CalculateOverlapDataTable(dscs, sscs);

        modelBuild.InsertIntoTable("DSC_SSC_OVERLAP", dt);
        if (modelBuild.TableExists("EMG3_Aggregated_DSC_CALCS"))
          modelBuild.DeleteTable("EMG3_Aggregated_DSC_CALCS");
        modelBuild.ExecuteQueryTable("EMG3_01_CalculateDscs", "QueryName", "dumpOrder");

        for (int i = 0; i < 2; i++)
        {
          if (_model.TimeFrame.ToUpper() == "EXISTING")
            modelBuild.ExecuteActionQuery("EMG3_UPDATE_SSC_CALCS_EX");
          else
            modelBuild.ExecuteActionQuery("EMG3_UPDATE_SSC_CALCS_FU");
        }


        _accessHelper.LinkTable("MODEL_DSCS_CALCULATED", modelBuildDbPath, "_temp_DSC_CALCS",  ExternalDatabaseType.MsAccess);
        _accessHelper.LinkTable("MODEL_SSCS_CALCULATED", modelBuildDbPath, "_temp_SSC_CALCS", ExternalDatabaseType.MsAccess);
        _accessHelper.Dispose();
        _accessHelper = new AccessHelper(_model.GetPathIfSet(ModelPathComponent.Pgdb));
        _accessHelper.CopyTable("_temp_DSC_CALCS", "MODEL_DSCS_CALCULATED");
        _accessHelper.CopyTable("_temp_SSC_CALCS", "MODEL_SSCS_CALCULATED");
        _accessHelper.DeleteTable("_temp_DSC_CALCS");
        _accessHelper.DeleteTable("_temp_SSC_CALCS");
      }
      catch (Exception ex)
      {
        throw ex;
      }
      finally
      {
        if (modelBuild != null)
          modelBuild.Dispose();
      }
      
    }

    private DataTable CalculateOverlapDataTable(ESRI.ArcGIS.Geodatabase.IFeatureClass dscs, ESRI.ArcGIS.Geodatabase.IFeatureClass sscs)
    {
      DataTable dt;
      Dictionary<int, Dictionary<int, double>> overlaps =
        sscs.CalculateOverlap(dscs);

      dt = new DataTable();
      dt.Columns.Add("overlap_id", typeof(int));
      dt.Columns.Add("ssc_objectid", typeof(int));
      dt.Columns.Add("dsc_objectid", typeof(int));
      dt.Columns.Add("overlap", typeof(double));
      int i = 0;

      foreach (KeyValuePair<int, Dictionary<int, double>> kvp in overlaps)
      {
        int sscOid = kvp.Key;

        foreach (KeyValuePair<int, double> kvp2 in kvp.Value)
        {
          int dscOid = kvp2.Key;
          double overlap = kvp2.Value;

          dt.Rows.Add(new object[] { i, sscOid, dscOid, overlap });
          i++;
        }
      }
      return dt;
    } 

  }

}
